DKSRV206:
SQL-JSON

USerdefined tablefunctions

JSON:

items = [
	{
		"item": "iPhone", 
		"price" : 123.55 , 
		"type" : "phone",
		"Manufact":"APPLE",
		"4Gsupprt":true,
		"pixels":"5M", 
		"stock" : 9
	},{
		"item": "Ixus Plus", 
		"price" :   812.00 , 
		"type" : "camera" , 
		"Manufact" : "CANON" , 
		"pixels" : "5M" ,
		"stock" : 5
	}   
]

_____________________________________


Select jsonGetStr(srcdta  ,'item') as ItemName 
from items                                     


Cast the property
-----------------
Select 
	cast(jsonGetStr(srcdta  ,'item')  as varchar(30)) as ItemName
from items                                     


Select 
	cast(jsonGetStr(srcdta  ,'type')   as char(10))    as Type,
	cast(jsonGetStr(srcdta  ,'item')   as varchar(30)) as ItemName, 
	cast(jsonGetStr(srcdta  ,'pixels') as varchar(10)) as Pixels , 
	cast(jsonGetNum(srcdta  ,'price')  as dec(7, 2))   as Price 
from items                                     



Adding the where
----------------
Select 
	cast(jsonGetStr(srcdta  ,'type')   as char(10))    as Type,
	cast(jsonGetStr(srcdta  ,'item')   as varchar(30)) as ItemName, 
	cast(jsonGetInt(srcdta  ,'pixels') as int       )  as Pixels, 
	cast(jsonGetNum(srcdta  ,'price')  as dec(7, 2))   as Price, 
	     jsonGetInt(srcdta  ,'stock')                  as Stock    
from items                                     
where jsonPropsIn (srcdta , 'type' , 'camera') = 1


Create is as a view
-------------------
create view qtemp/camera as (                                     
 Select                                                           
  cast(jsonGetStr(srcdta  ,'item')    as varchar(30)) as ItemName,
  cast(jsonGetNum(srcdta  ,'price')   as dec(7, 2))   as Price,   
  cast(jsonGetInt(srcdta  ,'pixels')  as int        ) as Pixels,  
       jsonGetInt(srcdta  ,'stock')                   as Stock    
 from items                                                       
 where jsonPropsIn (srcdta , 'type' , 'camera') = 1               
)                                                                 

Building up search 
------------------
Select 
	cast(jsonGetStr(srcdta  ,'type')      as char(10))    as Type,
	cast(jsonGetStr(srcdta  ,'item')      as varchar(30)) as ItemName, 
	cast(jsonGetStr(srcdta  ,'Manufact')  as varchar(20)) as Manufact, 
	cast(jsonGetNum(srcdta  ,'price')     as dec(7, 2))   as Price 
from items                                     
where jsonPropsIn (srcdta , 'Manufact' , 'NIKON,APPLE') = 1

Select 
	cast(jsonGetStr(srcdta  ,'type')      as char(10))    as Type,
	cast(jsonGetStr(srcdta  ,'item')      as varchar(30)) as ItemName, 
	cast(jsonGetStr(srcdta  ,'Manufact')  as varchar(20)) as Manufact, 
	cast(jsonGetNum(srcdta  ,'price')     as dec(7, 2))   as Price 
from items                                     
where jsonPropsLike (srcdta , 'item,Manufact' , 'canon') = 1



Select                                                             
 cast(jsonGetStr(srcdta  ,'type')      as char(10))    as Type,    
 cast(jsonGetStr(srcdta  ,'item')      as varchar(30)) as ItemName,
 cast(jsonGetStr(srcdta  ,'Manufact')  as varchar(20)) as Manufact,
 cast(jsonGetNum(srcdta  ,'price')     as dec(7, 2))   as Price,   
      jsonGetInt(srcdta  ,'stock')                     as Stock    
from items                                                         
where jsonPropsLikeIn (srcdta , 'item,Manufact' , 'ix') = 1        


update - set an object
----------------------

update items 
set srcdta = jsonSetObj(srcdta ,'p' , '{x:1,y:2}')


update - delete an object
-------------------------

update items 
set srcdta = jsonSetObj(srcdta ,'p' , 'null')


update - set a string atribute
------------------------------
update items 
set srcdta = jsonSetStr(srcdta ,'anystr' , 'Cool value')
 
                                                                         
Increment a numeric property                                          
------------------------------
update items                                                              
set    srcdta = jsonSetStr(srcdta, 'seq' ,                               
   cast (jsonGetInt(srcdta, 'seq') + 1  as varchar(3))         
)                                                                                                                     